import os
import pandas as pd
import pyautogui
import re
import datetime
import pytz
import xlrd
from faker import Faker
from config.config import DATA_PATH,DOWNLOAD_PATH,ALPHA_PORT,ALPHA_DB_HOST,DESKTOP_PATH
from tools.mongo_tool import MongoDbLink
import openpyxl
import random
import pymongo
from config.config import TEST_PORT,TEST_DB_HOST
mongo_link = pymongo.MongoClient(ALPHA_DB_HOST,ALPHA_PORT)
jw = MongoDbLink(TEST_DB_HOST,TEST_PORT,'alpha-test')
alpha = mongo_link.alpha
goat = mongo_link.goat_warehouse_dev
korea = mongo_link.goat_warehouse_korea


def write_xlsx_col():
    # 创建一个新的工作簿
    workbook = openpyxl.Workbook()
    # 选择活动工作表
    sheet = workbook.active
    # 假设我们要写入第2列（B列），从第2行开始
    column_index = 2  # 列索引，例如：1表示A列，2表示B列，以此类推
    start_row = 2  # 开始行号

    # 要写入的数据列表
    data = ['数据1', '数据2', '数据3', '数据4']

    # 将数据写入指定列
    for row_index, value in enumerate(data, start=start_row):
        cell_reference = f'B{row_index}'  # 根据列索引和行号构建单元格引用
        sheet[cell_reference] = value  # 将数据写入单元格

    # 保存工作簿到文件
    workbook.save(os.path.join(DATA_PATH,'dd.xlsx'))

write_xlsx_col()

def get_current_position():
    """获取当前鼠标所在坐标"""
    current_position = pyautogui.position()
    print("当前鼠标位置:", current_position)


def read_xlsx_file(file,num):
    """读取xlsx文件指定列内容"""
    df = pd.read_excel(file)
    li = []
    for i in df.iloc[:,num]:
        li.append(i)
    return li


def extract_numbers(s):
    # 使用正则表达式查找所有数字
    numbers = re.findall(r'\d+', s)
    # 如果需要返回单个字符串，可以将数字列表连接在一起
    return ''.join(numbers)


def extract_letters(input_string):
    # 使用正则表达式匹配所有字母（包括大小写）
    letters = re.findall(r'[a-zA-Z]', input_string)
    # 将找到的字母连接成一个字符串并返回
    return ''.join(letters)


def read_xls_file(file,sheet_index,col_index):
    """读取xls文件指定列内容"""
    workbook = xlrd.open_workbook(file)
    open_sheet = workbook.sheet_by_index(sheet_index)
    col_values = open_sheet.col_values(col_index)
    return [col_values]


def get_self_pickup_file(limit,skip):
    """创建goat自提入库单xlsx文件"""
    mobile_list = [17313699441,19071465376]
    email_list = [1596096369,3328287875]
    a = random.randint(10000, 99001)
    uniquecode_list = ['D' + str(a + i) for i in range(limit)]
    articlenumber_list = [i['sku'] for i in goat.goatskus.find({}).limit(limit).skip(skip)]
    data = [['唯一码', '货号', '尺码','手机号','邮箱','类型', '平台订单号']]
    for i in range(1,limit):
        data.append([uniquecode_list[i], articlenumber_list[i], random.randint(36, 44),
                     random.choice(mobile_list),
                     '{}@qq.com'.format(random.choice(email_list)),'Goat自提',
                     random.randint(10000000001,999999999999)])
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = '库存'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/goat_self_pickup.xlsx'
    workbook.save(file_path)


def get_deposit_file(limit,skip):
    """创建goat寄存预约单xlsx文件"""
    articlenumber_list = [i['articleNumber'] for i in jw.find_limit('spus',{},limit,skip)]
    merchantcode_list = [i['code'] for i in korea.merchants.find({})]
    data = [['货号', '规格','数量','商家编号']]
    for i in range(1,limit):
        data.append([articlenumber_list[i], random.randint(37, 42), random.randint(1, 10),
                     random.choice(merchantcode_list)])
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = '入库预约单'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/goat_depoist.xlsx'
    workbook.save(file_path)



def get_self_pickup_file_new(limit,skip):
    """创建goat最新自提入库单xlsx文件"""
    mobile_list = [17313699441,19071465376]
    email_list = [1596096369,3328287875]
    a = random.randint(10000, 99001)
    uniquecode_list = ['D' + str(a + i) for i in range(limit)]
    articlenumber_list = [i['sku'] for i in goat.goatskus.find({}).limit(limit).skip(skip)]
    data = [['订单号', '订单状态', '下单时间', '鉴定编号', '商品链接', '商品货号', '规格型号', '中文商品描述', '商品尺码 (US码)',
             '商品名称', '商品描述', '快递公司', '快递面单号', '收件人姓名', '用户手机', '收件人手机', '用户邮箱',
             '地址行1', '地址行2', '市', '州', '区', '数量', '零售价格', '订单价格','订单总价']]
    for i in range(1,limit):
        data.append([random.randint(10000000001,999999999999),'goat_pick',uniquecode_list[i], articlenumber_list[i], random.randint(36, 44),
                     random.choice(mobile_list),
                     '{}@qq.com'.format(random.choice(email_list)),'Goat自提',
                     random.randint(10000000001,999999999999)])
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = '库存'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/goat_self_pickup.xlsx'
    workbook.save(file_path)



def write_xlsx(title_array,array,name):
    """
    数据写入xlsx文件
    :param title_array:写入文档的首行，以字符串数组传递
    :param array:写入内容，每一行一个数组
    :param name:文件名
    """
    data = [title_array]
    data.extend(array)
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = 'sheet1'
    for row_index, row in enumerate(data):
        for col_index, value in enumerate(row):
            sheet.cell(row_index + 1, col_index + 1, value)
    file_path = f'{DATA_PATH}/{name}.xlsx'
    workbook.save(file_path)


def spu_sku_switch(file_path, spu_index, sku_index, name):
    """spu,sku转货号尺码"""
    spus = read_xlsx_file(file_path, spu_index)
    skus = read_xlsx_file(file_path, sku_index)
    li1, li2, li3 = [], [], []
    for spu in spus:
        [li1.append(result['articleNumber']) for result in alpha.spus.find({"spuId": spu})]
    for z in skus:
        for j in alpha.skus.find({"skuId": z}):
            li2.append(j['properties'].split('"尺码":"')[1].split('"')[0])

    #         if extract_numbers(j['properties']):
    #             li2.append(extract_numbers(j['properties']))
    #         else:
    #             li2.append(extract_letters(j['properties']))
    # for i in range(len(li2)):
    #     if len(li2[i]) == 3 and 'X' not in li2[i]:
    #         li2[i] = int(li2[i]) / 10
    for j in range(len(li1)):
        li3.append([li1[j], li2[j]])
    write_xlsx(['货号', '尺码'], li3, name)




def timestamp_to_date(file_path, num, title, name):
    """
    时间戳转为日期格式，写入导指定文件
    param file:需要读取的文件名
    param num:读取第几列
    param title:写入文件的标题
    param datas:写入的数据
    param name:新创建的文件名
    """
    times = read_xlsx_file(file=file_path, num=num)
    formatted_list = [datetime.datetime.utcfromtimestamp(times[i]/1000).strftime('%Y-%m-%d %H:%M:%S') for i in range(len(times))]
    time_datas = [[i] for i in formatted_list]
    write_xlsx(title, time_datas, name)


# file = os.path.join(DOWNLOAD_PATH,'小超空单（0409）.xlsx')
# spus = read_xlsx_file(file,0)
# skus = read_xlsx_file(file,1)
# li = []
# for i in range(len(spus)):
#     num = korea.spus.count_documents({"articleNumber": i})
#     li.append(num)
# print(li)



# # timestamp_to_date(file_path=file, num=0,title=['创建日期'], name='出库单')
# orders = read_xlsx_file(file,1)
# li = []
# for i in orders:
#     [li.append(z['total']) for z in alpha.outstockfees.find({"outStockOrderNo":i})]
# li1 = [[z] for z in li]
# write_xlsx(['jine'],li1,'price')
# a = read_xlsx_file(file=file,num=5)
# for i in range(len(a)):
#     if len(str(a[i])) == 3:
#         a[i] = a[i]/10
# zz = [[t]for t in a]
# write_xlsx(['尺码'],zz,'dddd')
# a = '{"颜色":"黑色","尺码":"S"}'
# spu_sku_switch(file_path=file, spu_index=0, sku_index=1, title=['货号', '尺码'], name='xxx')

# res = alpha.branddeliverreturns.aggregate([{
#     "$lookup": {
#         "from": "orders",
#         "localField": "order_no",
#         "foreignField": "orderNo",
#         "as": "result",
#       },
#   },
#   {
#     "$unwind":
#       {
#         "path": "$result",
#       },
#   },
#   {
#     "$group":
#       {
#         "_id": {
#           "closetype": "$result.closeType",
#           "status": "$result.orderStatus",
#         },
#         "total": {
#           "$sum": 1,
#         },
#       },
#   },
#   {
#     "$project":
#       {
#         "closetype": "$_id.closetype",
#         "orderStatus": "$_id.status",
#         "total": 1,
#         "_id": 0,
#       },
#   }
# li = read_xlsx_file(os.path.join(DESKTOP_PATH,'test1.xlsx'),0)
# """集物重复订单"""
# li2 = []
# dic = {}
# [li2.append(i['orderNo']) for i in alpha.orders.find({})]
# for i in li2:
#     if i not in dic:
#         dic[i]=1
#     else:
#         dic[i]+=1
#
#
# def find_keys_by_value(dictionary, value):
#     return [key for key, val in dictionary.items() if val == value]
#
#
# # 使用函数查找值为2的键
# keys_with_value_2 = find_keys_by_value(dic, 2)
# print(keys_with_value_2)  # 输出: ['b', 'd']
# file = os.path.join(DESKTOP_PATH,'123.xlsx')
# spus = read_xlsx_file(file,5)
# skus = read_xlsx_file(file,6)
# sp,sk= [],[]
# for z in spus:
#     [sp.append([i['articleNumber']]) for i in goat.spus.find({'spuId':z})]
# for j in spus:
#     [sp.append([i['properties']]) for i in goat.skus.find({'skuId': j})]

# spu_sku_switch(file_path=file,spu_index=2,sku_index=3,name='123.xlsx')
# # a = read_xlsx_file(file,9)
# timestamp_to_date(file_path=file,num=10,title='上架时间',name='dd')
# file1 = os.path.join(DESKTOP_PATH,'123.xlsx')
# sp = read_xlsx_file(file1,5)
# sk = read_xlsx_file(file1,6)
# spu_sku_switch(file1,5,6,'122')
# timestamp_to_date(file1,0,'123','22222')

def korea_stock_deposit_export():
    """韩国仓库存导出"""
    file_path = os.path.join(DESKTOP_PATH, '123.xlsx')
    created = read_xlsx_file(file_path,0)
    unique = read_xlsx_file(file_path,2)
    appointno = read_xlsx_file(file_path,3)
    spus = read_xlsx_file(file_path,5)
    skus = read_xlsx_file(file_path,6)
    instock = read_xlsx_file(file_path,7)
    waitout = read_xlsx_file(file_path,9)
    now_stock = read_xlsx_file(file_path,8)
    is_fail = read_xlsx_file(file_path,11)
    shelf = read_xlsx_file(file_path,12)
    merchant = read_xlsx_file(file_path,14)
    li = []
    for i in range(len(created)):
        li.append([appointno[i],unique[i],spus[i],skus[i],instock[i],instock[i]-waitout[i]-now_stock[i],waitout[i],now_stock[i],is_fail[i],shelf[i],merchant[i]])

